#! /usr/bin/env python  
# -*- coding: utf-8 -*-  
# __author__ = "shuke"  
# Date: 2018/5/13  
import hashlib   
import pymysql  
from dbutils.pooled_db import PooledDB
import sys,os
sys.path.append('D:\wwwroot\Python\tool\src')
from utils.tool import Tool

class QuestionsTool:

    # 创建数据库连接池
    tempPool = PooledDB(
        creator=pymysql,  # 使用链接数据库的模块
        maxconnections=6,  # 连接池允许的最大连接数
        mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
        maxcached=5,  # 链接池中最多闲置的链接
        maxshared=3,  # 链接池中最多共享的链接数量
        blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待
        host='192.168.10.191',
        port=3306,
        user='writer',
        password='zlc1002020',
        database='wisdom-exam-db',
        charset='utf8mb4'  # 设置编码
    )

    examPool = PooledDB(
        creator=pymysql,  # 使用链接数据库的模块
        maxconnections=6,  # 连接池允许的最大连接数
        mincached=2,  # 初始化时，链接池中至少创建的空闲的链接
        maxcached=5,  # 链接池中最多闲置的链接
        maxshared=3,  # 链接池中最多共享的链接数量
        blocking=True,  # 连接池中如果没有可用连接后，是否阻塞等待
        host='192.168.10.191',
        port=3306,
        user='writer',
        password='zlc1002020',
        database='wisdom-exam-db',
        charset='utf8mb4'  # 设置编码
    )

    # 获取数据
    def getScriptQuestions():
        global tempPool  # 指定global_subjectId是全局变量
        conn = tempPool.connection()
        cursor = conn.cursor()

        # 执行SQL语句
        sql = "SELECT id,topic_key,subject_id,section_id,stem,answer,analysis,choicesList,year,knowledge_ids,subQuestions FROM script_questions WHERE topic_id = 0 AND is_sys = 0 AND has_sub = 0 AND is_new = 0 limit 500"
        cursor.execute(sql)

        # 获取查询结果
        result = cursor.fetchall()

        # 不关闭连接，而是将连接放回连接池
        cursor.close()
        conn.close()
        
        return result
    
    # 获取试题
def getTopicsInfo(topicKey):
    global examPool  # 指定global_subjectId是全局变量
    conn = examPool.connection()
    cursor = conn.cursor()

    topic_key_hash = Tool.md5_32(topicKey)

    # 执行SQL语句
    sql = "SELECT id FROM topics_info WHERE topic_key_hash='"+topic_key_hash+"'"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

# 获取试题详情
def getTopicsDetail(topicKey):
    global examPool  # 指定global_subjectId是全局变量
    conn = examPool.connection()
    cursor = conn.cursor()

    topic_key_hash = Tool.md5_32(topicKey)

    # 执行SQL语句
    sql = "SELECT id FROM topics_detail WHERE topic_key_hash='"+topic_key_hash+"'"
    cursor.execute(sql)

    # 获取查询结果
    result = cursor.fetchall()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()
    
    return result

# 保存试题
def saveTopicsInfo(row):
    global examPool  # 指定global_subjectId是全局变量
    # 插入数据初始化 id,topic_key,subject_id,section_id,stem,answer,analysis,choicesList,year,knowledge_ids,subQuestions
    topic_key = str(row[1])
    subject_id = str(row[2])
    learn_phase = '3'
    section_id = str(row[2])
    knowledges = str(row[9])
    year = str(row[8])
    source = 2
    topic_key_hash = Tool.md5_32(topic_key)

    # 执行SQL语句
    hasResult = getTopicsInfo(row[1])
    hasLength = len(hasResult)
    conn = examPool.connection()
    cursor = conn.cursor()

    if hasLength == 0:
      # 执行插入数据操作
      sql = "INSERT INTO topics_info (topic_key,subject_id,learn_phase,section_id,knowledges,year,source,topic_key_hash) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
      values = (topic_key, subject_id, learn_phase, section_id, knowledges, year, source,topic_key_hash)
      cursor.execute(sql, values)
      # 提交事务
      conn.commit()

    else:  
      #更新单行数据
      sql = "UPDATE topics_info SET topic_key = %s,subject_id = %s,learn_phase =%s,section_id =%s, knowledges =%s, year =%s, source =%s, topic_key_hash =%s WHERE topic_key_hash = %s"
      cursor.execute(sql, (topic_key, subject_id, learn_phase, section_id, knowledges, year, source,topic_key_hash,topic_key_hash))
      # 提交事务
      conn.commit()

    # 查询插入的数据
    hasResult = getTopicsInfo(row[1])    
    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()   

    return hasResult

# 保存试题详情      
def saveTopicsDetail(row,topicId):
    global examPool  # 指定global_subjectId是全局变量
    # 插入数据初始化 id,topic_key,subject_id,section_id,stem,answer,analysis,choicesList,year,knowledge_ids,subQuestions
    topic_key = str(row[1])             # $row['topic_key']
    content = str(row[4])               # $row['stem'],
    answer = str(row[5])                # $row['answer'],
    display_answer = str(row[5])        # $row['answer'],
    analyse = str(row[6])               # $row['analysis']
    options = str(row[7])               # $row['choicesList']
    source = 2                          # 数据来源
    topic_key_hash = Tool.md5_32(topic_key)  # 索引字段

     # 执行SQL语句
    hasResult = getTopicsDetail(row[1])
    hasLength = len(hasResult)
    conn = examPool.connection()
    cursor = conn.cursor()

    if hasLength == 0:
      # 执行插入数据操作
      sql = "INSERT INTO topics_detail (topic_id,topic_key,content,answer,display_answer,analyse,options,source,topic_key_hash) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)"
      values = (topicId,topic_key, content, answer, display_answer, analyse, options, source,topic_key_hash)
      cursor.execute(sql, values)
      # 提交事务
      conn.commit()

    else:
      #更新单行数据
      sql = "UPDATE topics_detail SET topic_id = %s,topic_key = %s,content =%s,answer =%s, display_answer =%s, analyse =%s, options =%s, source =%s,topic_key_hash =%s  WHERE topic_key_hash = %s"
      cursor.execute(sql, (topicId,topic_key, content, answer, display_answer, analyse, options, source,topic_key_hash,topic_key_hash))
      # 提交事务
      conn.commit()

    # 查询插入的数据
    hasResult = getTopicsDetail(row[1])
    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()

    return hasResult

def updateScriptQuestions(id,topicId):
    global tempPool  # 指定global_subjectId是全局变量
    conn = tempPool.connection()
    cursor = conn.cursor()

    # 执行SQL语句
    #更新单行数据
    sql = "UPDATE script_questions SET topic_id = %s,is_sys = 1 WHERE id = %s"
    cursor.execute(sql, (topicId,id))

    #提交事务
    conn.commit()

    # 不关闭连接，而是将连接放回连接池
    cursor.close()
    conn.close()

def tests():
   print(666)    